﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace SQLCheck
{
    /// <summary>
    /// 校验
    /// </summary>
    public class Check
    {
        /// <summary>
        /// 正则检索出join语句
        /// </summary>
        const string regexString = @"select([\s\S]+?)(from)([\s\S]+?)(join)";

        /// <summary>
        /// 文件路径
        /// </summary>
        readonly string filePath = "";

        /// <summary>
        /// 忽略的字符串
        /// </summary>
        private static List<string> ignoreString = new List<string>()
        {
            "newid()",
            "getdate()"
        };

        /// <summary>
        /// 检查*号
        /// </summary>
        public bool CheckALL { get; set; } = false;

        /// <summary>
        /// 校验
        /// </summary>
        /// <param name="filePath">文件路径</param>
        public Check(string filePath)
        {
            this.filePath = filePath;
        }


        /// <summary>
        /// 校验
        /// </summary>
        /// <param name="filePath">文件路径</param>
        public Check(string filePath, bool checkALL)
        {
            this.filePath = filePath;
            this.CheckALL = checkALL;
        }

        /// <summary>
        /// 开始校验
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, List<string>> Start()
        {
            Dictionary<string, List<string>> dic = new Dictionary<string, List<string>>();
            //List<string> list = new List<string>();
            string content = File.ReadAllText(filePath);

            GetALL(content, dic);

            return dic;
        }


        private void GetALL(string content, Dictionary<string, List<string>> list)
        {
            //截取出第一字符，如果匹配不到则表示为最小值了
            string result = content.Substring(1);
            var matchs = getMatchCollection(regexString, result);
            if (matchs.Count == 0)
            {
                if (content.ToLower().Contains("update") ||
                    content.GetAllStringCount("(") != content.GetAllStringCount(")"))//不等于表示是子查询（此处会查不到父级）
                {
                    return;
                }
                //此时content就是查询出的结果了
                var ma = getMatchCollection(regexString, content);
                if (ma.Count == 0)
                {
                    return;
                }
                string selectTofromValue = Convert.ToString(ma[0].Groups[1]) ?? "";
                //效验*
                if (CheckALL && selectTofromValue.Contains("*"))
                {

                    list.AddValue(content, "*");
                    return;
                }

                //效验是否是有别名
                var l = selectTofromValue.Split(',').ToList();
                var reslut = GetRight(l);
                foreach (var item in reslut)
                {


                    if (CheckIgnore(item.ToLower()))
                        continue;
                    //判读能否转换数字
                    if (int.TryParse(item, out int ints))
                        continue;
                    //判断是否为变量
                    if ((item.StartsWith("@") || item.StartsWith("null")) && !item.ToLower().Contains("as"))
                    {
                        continue;
                    }

                    var arr = item.ToLower().Split(' ');
                    if (arr.Count() > 1)
                    {
                        string str = string.Join("", arr.Take(arr.Length - 1)).Trim().ToLower();
                        //字符串常量
                        if (item.StartsWith("'") && item.GetAllStringCount("'") == 2 &&
                           (str.EndsWith("'") || str.EndsWith("as")))
                        {
                            continue;
                        }
                        //数字常量
                        if (str.EndsWith("as"))
                        {
                            str = str.Substring(0, str.Length - 2);
                        }
                        if (int.TryParse(str, out ints))
                            continue;
                        //判断是否为变量
                        if ((str.StartsWith("@") || item.StartsWith("null")) && arr[0] == str)
                        {
                            continue;
                        }
                    }
                    //开头结尾为 '' 符号，切分割长度为三，则认为是一个字符串 
                    if (item.StartsWith("'") && item.EndsWith("'"))//&& item.Split('\'').Count() == 3)
                    {
                        continue;
                    }
                    if (!item.Contains("."))
                    {
                        list.AddValue(content, item.ToLower());
                        continue;
                        //return; 查出所有的字段
                    }
                }

            }

            for (int i = 0; i < matchs.Count; i++)
            {
                string res = matchs[i].ToString();
                GetALL(res, list);
            }
        }


        /// <summary>
        /// 检查是否含有忽略项
        /// </summary>
        /// <param name="str"></param>
        /// <returns>true有</returns>
        private bool CheckIgnore(string str)
        {
            foreach (var ignore in ignoreString)
            {
                if (str.Contains(ignore))
                {
                    return true;
                }
            }
            return false;
        }


        /// <summary>
        /// 括号不成对则表示有问题，订正
        /// </summary>
        /// <returns></returns>
        private List<string> GetRight(List<string> list)
        {
            List<string> result = new List<string>();
            string str = "";
            for (int i = 0; i < list.Count(); i++)
            {
                str += list[i];
                if (list[i].GetAllStringCount("(") == list[i].GetAllStringCount(")"))
                {
                    str = str.ToLower().Replace("distinct","");
                    result.Add(str.Trim());
                    str = "";
                }
            }
            return result;
        }




        /// <summary>
        /// 获取正则结果
        /// </summary>
        /// <param name="regText">正则表达式</param>
        /// <param name="content">待匹配内容</param>
        /// <returns></returns>
        protected string getRegex(string regText, string content)
        {
            Regex reg = new Regex(regText, RegexOptions.IgnoreCase);
            MatchCollection matchs = reg.Matches(content);
            return matchs[0].Groups[1].ToString();
        }

        Regex reg = new Regex(regexString, RegexOptions.IgnoreCase);
        /// <summary>
        /// 获取正则结果
        /// </summary>
        /// <param name="regText">正则表达式</param>
        /// <param name="content">待匹配内容</param>
        /// <returns></returns>
        protected MatchCollection getMatchCollection(string regText, string content)
        {

            return reg.Matches(content);
        }


    }


    public static class stringEx
    {
        /// <summary>
        /// 返回一个字符在字符串出现的次数
        /// </summary>
        /// <param name="s"></param>
        /// <param name="re"></param>
        /// <returns></returns>
        public static int GetAllStringCount(this string s1, string re)
        {
            int count = 0;
            int startIndex = 0;
            while (true)
            {
                int y = s1.IndexOf(re, startIndex);
                if (y != -1)
                {
                    count++;
                    startIndex = y + 1;
                }
                else
                {
                    break;
                }
            }

            return count;
        }


        public static void AddValue(this Dictionary<string, List<string>> dic, string key, string value)
        {
            if (dic.Keys.Contains(key))
            {
                dic[key].Add(value);
            }
            else
            {
                dic.Add(key, new List<string>() { value });
            }
        }
    }


}
